#!/usr/bin/env python
''' Module for postgreSQL interface to the firelog database
    Usage (to be run interactively above the pyanalyze directory):
'''
       

from __future__ import with_statement, absolute_import, print_function
import psycopg2
from pprint import pprint
import operator

# only for logging
import logging
import sys
import time
import os

# config file
from . import config_pyanalyze


class PyAnalyzeDatabase:
    

    _table_name = None
    _table_name_res = None
    created = None
    database = None
    
    
    def __init__(self, database):
        '''Initialize the database object'''
        
        
        try:
            # isolation_level in order to auto-commit
            self.py_conn = psycopg2.connect("dbname=firelog user=salvo")
        except psycopg2.Error, mes:
            print(''.join((
                'Unable to connect to the database: ', database,
                '\nError message: ', str(mes))))
            self.created = False
            return
        print(' '.join(("Created connection to data base",
                      # "Database:", os.path.basename(database))))
                      "Database:", database)))
        
        if database != '000000000':
            self.created = True   
        self.database = database
        #urls_data = user.fetch_browsed_with_order('session_start')
        #cmd = ' '.join(("SELECT * FROM", self._table_name,"WHERE clientID =",self.database))
        
        #print(cmd)
        #self.py_cursor.execute(cmd)
        #data_rcv = self.py_cursor.fetchall()
        #print(data_rcv)
        
        # Modified for students VM
        self._table_name = "plugin_raw_sid"
        
        self._table_name_res = "a_salvo_clear_1a6_web_results"
        self.py_cursor = self.py_conn.cursor()
        
    
    
    def get_clientID(self):
        ''' Function only for students VM'''
                        
        cmd_if = "select tablename from pg_tables where schemaname = 'public' and tablename = 'plugin_raw_sid' "
        self.py_cursor.execute(cmd_if)
        if_fetch = self.py_cursor.fetchall()
        #print(str(if_fetch))
        if not if_fetch:
            print("SID table does NOT exist")     
            cmd_sid = "select * from gen_table_sid('plugin_raw','plugin_raw_sid')" 
            self.py_cursor.execute(cmd_sid)
            #print(cmd_sid)
            sid_table = self.py_cursor.fetchall()
            self.py_conn.commit()
             
        cmd = ' '.join(("SELECT clientID FROM", self._table_name,"LIMIT 1"))
        
        print(cmd)
        self.py_cursor.execute(cmd)
        return self.py_cursor.fetchall()[0][0]
        
    def get_tablenames(self):
        ''' Function to get the user tablenames
            It return a record like....
            [[webinterface_table], [yesterday_table],[today_table],[born_table]] '''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        
        cmd = ' '.join(('SELECT * FROM gen_user_web_table(', self.database,
                        ") as r(webinterface varchar, yesterday varchar, today varchar, born varchar)" ))
        #cmd = "select name from firelog_metadata"
        #print(cmd)
        self.py_cursor.execute(cmd)
        tables = self.py_cursor.fetchall()
        print(tables[0][0])
            
        if len(tables) >= 1:
            print("yes")
            self._table_name = tables[0][0]
        else:
            print("No tables found in database")
       
        #self.py_cursor.execute(cmd)
        return tables[0][0]
        
        
    # it can be useful but not used at moment
    def fetch_all(self):
        ''' Function to print all the records of the table'''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        
        if not self._table_name:
            self._table_name = self.get_tablenames()
            

        cmd = ' '.join(("SELECT * FROM", self._table_name,"WHERE clientID =",self.database))
        
        self.py_cursor.execute(cmd)

    
    # it can be useful but not used at moment   / to check
    def describe_tables(self):
        '''Function to show the create command of a table'''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        
        if not self._table_name:
            tablenames = self.get_tablenames()
            print(tablenames)

        cmd = "SELECT sql FROM firelog_metadata WHERE type = 'table'"
        self.py_cursor.execute(cmd)
        for record in self.py_cursor:
            pprint(record)

       
    # it can be useful but not used at moment
    def fetch_single_parameter_with_stats(self, parameter):
        '''Function to save (timestamp, parameter) in a sorted list of tuples
        only for records with stats
        '''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name:
            tablenames = self.get_tablenames()
            print(tablenames)

        cmd = ' '.join(('SELECT DISTINCT on (session_start) sid, %s FROM' % parameter, self._table_name,
                        "WHERE clientID = ",self.database))
        
        self.py_cursor.execute(cmd)
        return sorted(self.py_cursor.fetchall(), key=operator.itemgetter(0))
    
    
    def fetch_browsed_with_order(self, parameter):
        '''
        '''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name:
            
            tablenames = self.get_tablenames()
            print(tablenames)

        cmd = ' '.join(("SELECT DISTINCT on (%s) sid, session_start, session_url,"
                        "case when max(full_load_time)>0 then max(full_load_time) "
                        "else max(extract(minute from endtime-session_start)*60*1000+extract(millisecond from endtime-session_start)) "
                        "end as PLT_or_ABORT_time FROM" 
                        % parameter, self._table_name,'WHERE clientID = %s group BY sid,session_url,' % self.database, parameter))
        
        self.py_cursor.execute(cmd)
        return self.py_cursor.fetchall()
    
    
    def fetch_metrics_with_order(self, parameter):
        '''
        '''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name:
            tablenames = self.get_tablenames()
            print(tablenames)

        cmd = ''.join(("SELECT t1.sid, t1.session_start, avg_dns, avg_http, avg_tcp,  PLT_or_ABORT_time from" 

                    "(SELECT sid, session_start, case when dns>0 then dns else 0 end as avg_dns, PLT_or_ABORT_time from" 

                    "(SELECT sid, session_start, avg(case when dns_time>0 then dns_time else null end) as dns, case when max(full_load_time)>0 then max(full_load_time) else max(extract (minute from endtime-session_start)*60*1000+extract(millisecond from endtime-session_start)) end as PLT_or_ABORT_time " 
                    "from " , self._table_name," where clientID = ",self.database," GROUP BY sid, session_start)t "

                    "GROUP BY sid,dns, session_start, PLT_or_ABORT_time  ORDER BY sid)t1, "

                    "(SELECT sid,session_start, case when tcp>0 then tcp else 0 end as avg_tcp from (SELECT sid, session_start, avg(case when tcp_cnxting >0 then tcp_cnxting else null end) as tcp "
                    "from " , self._table_name," where clientID = ",self.database," GROUP BY sid, session_start)t GROUP BY sid,tcp, session_start  ORDER BY sid)t2, "

                    "(SELECT sid,session_start, case when http>0 then http else 0 end as avg_http from (SELECT sid, session_start, avg(case when app_rtt >0 then app_rtt else null end) as http "
                    "from " , self._table_name," where clientID = ",self.database," GROUP BY sid, session_start)t GROUP BY sid,http, session_start  ORDER BY sid)t3 " 
                    "where t1.sid=t2.sid and t1.sid=t3.sid;" ))
        
        #print(cmd)
        self.py_cursor.execute(cmd)
        return self.py_cursor.fetchall()
    
    
    def count_rows(self):
        '''Function to check if there is any browsing session saved into the db'''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name:
            tablenames = self.get_tablenames()
            print(tablenames)
        
        #cmd = ' '.join(('SELECT count(sid) FROM (SELECT sid FROM', self._table_name,
        #                "WHERE clientID= %s GROUP BY sid ORDER BY sid) as session" % self.database ))
        cmd = ' '.join(('SELECT count(session_start) FROM (SELECT DISTINCT on (session_start) session_start FROM plugin_raw',
                        "WHERE clientID = %s ) as tmp_session" % self.database ))
    
        #select count (session_start) from (SELECT DISTINCT on (session_start) session_start  from plugin_raw where clientID = '213343835')as da
    
        # print(cmd)
        self.py_cursor.execute(cmd)
        return self.py_cursor.fetchall()[0][0]

    def drop_sid_table(self):
        '''Function to check if there is any browsing session saved into the db'''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name:
            tablenames = self.get_tablenames()
            print(tablenames)
                   
        cmd = ' '.join(('DROP TABLE IF EXISTS ', self._table_name))
                        
        #select count (session_start) from (SELECT DISTINCT on (session_start) session_start  from plugin_raw where clientID = '213343835')as da
        
        print("DROP TABLE")
        self.py_cursor.execute(cmd)
        self.py_conn.commit()
    
    def count_rows_results(self):
        '''Function to check if there is any browsing session saved into the db'''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name_res:
            cmd = "select name from firelog_results_metadata"
            self.py_cursor.execute(cmd)
            table = self.py_cursor.fetchall()
            if len(table) == 1:
                self._table_name_res = table[0][0]
            else:
                print("No tables found in database")
                return
        cmd = ' '.join(('SELECT count(sid) FROM (SELECT sid FROM', self._table_name_res,
                        "GROUP BY sid ORDER BY sid) as session"))
        
        self.py_cursor.execute(cmd)
        return self.py_cursor.fetchall()[0][0]
    
    # TO DO 
    def fetch_diagnosis_results(self):
        '''Function to check if there is any browsing session saved into the db'''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name_res:
            cmd = "select name from firelog_results_metadata"
            self.py_cursor.execute(cmd)
            table = self.py_cursor.fetchall()
            if len(table) == 1:
                self._table_name_res = table[0][0]
            else:
                print("No tables found in database")
                return
        # Client GAP
        cmd = ' '.join(("SELECT sid,session_start,load_or_abort_time,res FROM "
                        "(SELECT sid, session_start,load_or_abort_time, split_part(result,'ms = ',2) as res FROM" 
                        , self._table_name_res,"where result like '%Client%') as risultati"))
        self.py_cursor.execute(cmd)
        diagnosis = []
        diagnosis.append(self.py_cursor.fetchall())
        
        # Server side delay
        cmd = ' '.join(("SELECT sid,session_start,load_or_abort_time,res FROM "
                        "(SELECT sid, session_start,load_or_abort_time, split_part(result,'avg(HTTP) = ',2) as res FROM" 
                        , self._table_name_res,"where result like '%Server%') as risultati"))
        self.py_cursor.execute(cmd)
        diagnosis.append(self.py_cursor.fetchall())
        
        # Local Contribution
        cmd = ' '.join(("SELECT sid,session_start,load_or_abort_time,res FROM "
                        "(SELECT sid, session_start,load_or_abort_time, split_part(result,'avg(Curr_TCP) = ',2) as res FROM" 
                         , self._table_name_res,"where result like '%Local Access Limitation - IP%') as risultati"))
        #print(cmd)
        self.py_cursor.execute(cmd)
        diagnosis.append(self.py_cursor.fetchall())
        
        # Local Normalize
        cmd = ' '.join(("SELECT sid,session_start,load_or_abort_time,res FROM "
                        "(SELECT sid, session_start,load_or_abort_time, split_part(result,'avg(Curr_TCP) = ',2) as res FROM" 
                         , self._table_name_res,"where result like '%Normalized%') as risultati"))
        #print(cmd)
        self.py_cursor.execute(cmd)
        diagnosis.append(self.py_cursor.fetchall())
        
        # WILD INTERNET
        cmd = ' '.join(("SELECT sid,session_start,load_or_abort_time,res FROM "
                        "(SELECT sid, session_start,load_or_abort_time, split_part(result,'avg(Curr_TCP) = ',2) as res FROM" 
                        , self._table_name_res,"where result like '%Wild Internet%') as risultati"))
        #print(cmd)
        self.py_cursor.execute(cmd)
        diagnosis.append(self.py_cursor.fetchall())
        
        return diagnosis
        
        
    def fetch_single_parameter(self, parameter):
        '''Function to save (timestamp,parameter) in a sorted list of tuples'''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name:
            tablenames = self.get_tablenames()
            print(tablenames)

        cmd = ' '.join(("SELECT DISTINCT on (session_start) %s FROM" % parameter,
                        self._table_name,"WHERE clientID= %s" % self.database))
        
        self.py_cursor.execute(cmd)
        return sorted(self.py_cursor.fetchall(), key=operator.itemgetter(0))
    

    # Perhaps useful in future
    def fetch_start_time(self):
        '''Function to return the first timestamp in the database in linux
        format
        '''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name:
            tablenames = self.get_tablenames()
            print(tablenames)

        cmd = ' '.join(("SELECT ID FROM", self._table_name))
        self.py_cursor.execute(cmd)
        try:
            timestamp = time_to_epoch(min(self.py_cursor.fetchall())[0])
        except ValueError:
            timestamp = None
        return timestamp


    def close_handle(self):
        "Closes the connection to the database"
        if not self.created:
            print('Database could not be created\n'
                                   'Close aborted')
            return
        self.py_conn.close()



    def fetch_min_time(self):
        '''Function to return the last timestamp in the database in linux
        format
        '''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        if not self._table_name:
            tablenames = self.get_tablenames()
            print(tablenames)

        try:
            timestamp = time_to_epoch(min(self.fetch_single_parameter('session_start'))[0])
        except ValueError:
            timestamp = None
        return timestamp
    
    def insert_user(self):
        '''Function to insert a new user in the database 
        '''
        if not self.created:
            print('Database could not be created\n'
                                   'Fetch aborted')
            return
        
        cmd = ''.join(("INSERT INTO users (clientID) SELECT "
                       , self.database," WHERE NOT EXISTS (SELECT clientID FROM users WHERE clientID = "
                       ,self.database," )"))
        #print(cmd)
        
        try:
            self.py_cursor.execute(cmd)
            self.py_conn.commit()
        except psycopg2.Error, mes:
            print('unable to add new user: %s with error: %s'
                                    % (self.database, mes))
            
        if int(self.py_cursor.statusmessage[9]):
            print('New User')
            config_pyanalyze.LOG.critical('New user inserted into db %s', str(self.database))
        else:
            print('User already in the db')
    
    def fetch_ext_rows(self):
        cmd = ''.join(("SELECT nr_row FROM users WHERE clientID = "
                       ,self.database))
        #print(cmd)
        self.py_cursor.execute(cmd)
        return self.py_cursor.fetchall()[0][0]
    
    def set_ext_rows(self,nr_rows):
        cmd = ''.join(("UPDATE users SET nr_row = ",str(nr_rows)," WHERE clientID = "
                       ,self.database))
        #print(cmd)
        self.py_cursor.execute(cmd)
        self.py_conn.commit()
        # print(self.py_cursor.statusmessage)
    
    
    
# TODO
def time_to_epoch(timestamp):
    ''' Function to transform to seconds from epoch time represented by a
    string of the form '%Y-%m-%d %H:%M:%S.%f'
    >>> time_to_epoch('2012-06-25 14:54:57.422007')
    1340628897
    >>> time_to_epoch(None)
    Traceback (most recent call last):
        ...
    TypeError: expected string or buffer
    >>> time_to_epoch('2012-06-25 14:54:57')
    1340628897
    >>> time_to_epoch('2012-06-25 14:54:57') #doctest: +NORMALIZE_WHITESPACE
    Traceback (most recent call last):
        ...
    ValueError: time data '2012-06-25 14:54:57' does not match format
    '%Y-%m-%d %H:%M:%S.%f'
    '''
    print('timestamp_epoch',timestamp)
    try:
        return int(time.mktime(time.strptime(str(timestamp),
                                             "%Y-%m-%d %H:%M:%S.%f")))
    except ValueError:
        return int(time.mktime(time.strptime(timestamp, "%Y-%m-%d %H:%M:%S")))

if __name__ == '__main__':
    import doctest
    doctest.testmod()
